
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 01/14/2011 01:20:54
-- Generated from EDMX file: E:\Documentos\Visual Studio 2010\Projects\ControlTiempo\ControlTiempo\ControlTiempo.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [CT_DB];
GO
IF SCHEMA_ID(N'ct') IS NULL EXECUTE(N'CREATE SCHEMA [ct]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------


-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------


-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Proyectos'
CREATE TABLE [ct].[Proyectos] (
    [Codigo] int IDENTITY(1,1) NOT NULL,
    [PrioridadCodigo] int  NOT NULL,
    [DepartamentoCodigo] int  NOT NULL,
    [EstadoCodigo] int  NOT NULL,
    [IdCentroProyectos] nvarchar(max)  NOT NULL,
    [Nombre] nvarchar(200)  NOT NULL,
    [FechaInicio] datetime  NOT NULL,
    [FechaFinEstimada] datetime  NOT NULL,
    [FechaCierre] datetime  NOT NULL
);
GO

-- Creating table 'Prioridades'
CREATE TABLE [ct].[Prioridades] (
    [Codigo] int IDENTITY(1,1) NOT NULL,
    [Nombre] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'Departamentos'
CREATE TABLE [ct].[Departamentos] (
    [Codigo] int IDENTITY(1,1) NOT NULL,
    [Nombre] nvarchar(200)  NOT NULL
);
GO

-- Creating table 'Tareas'
CREATE TABLE [ct].[Tareas] (
    [Codigo] int IDENTITY(1,1) NOT NULL,
    [Descripcion] nvarchar(500)  NOT NULL,
    [UsuarioCodigo] int  NOT NULL,
    [DepartamentoPeticionario_Codigo] int  NOT NULL,
    [ProyectoAsociado_Codigo] int  NOT NULL
);
GO

-- Creating table 'Fases'
CREATE TABLE [ct].[Fases] (
    [Codigo] int IDENTITY(1,1) NOT NULL,
    [Nombre] nvarchar(50)  NOT NULL,
    [Tareas_Codigo] int  NOT NULL
);
GO

-- Creating table 'Estados'
CREATE TABLE [ct].[Estados] (
    [Codigo] int IDENTITY(1,1) NOT NULL,
    [Nombre] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'Tipos'
CREATE TABLE [ct].[Tipos] (
    [Codigo] int IDENTITY(1,1) NOT NULL,
    [Nombre] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'Usuarios'
CREATE TABLE [ct].[Usuarios] (
    [Codigo] int IDENTITY(1,1) NOT NULL,
    [Login] nvarchar(50)  NOT NULL,
    [DepartamentoCodigo] int  NOT NULL,
    [UsuarioResponsable_Usuario_Codigo] int  NOT NULL
);
GO

-- Creating table 'Registros'
CREATE TABLE [ct].[Registros] (
    [Codigo] int IDENTITY(1,1) NOT NULL,
    [TareaCodigo] int  NOT NULL,
    [FechaInicio] datetime  NOT NULL,
    [FechaFin] datetime  NOT NULL,
    [Duracion] nvarchar(max)  NOT NULL,
    [TipoCodigo] int  NOT NULL
);
GO

-- Creating table 'Tareas_TareaGestion'
CREATE TABLE [ct].[Tareas_TareaGestion] (
    [DepartamentoCodigo] int  NOT NULL,
    [Codigo] int  NOT NULL
);
GO

-- Creating table 'Tareas_TareaMantenimiento'
CREATE TABLE [ct].[Tareas_TareaMantenimiento] (
    [DepartamentoCodigo] int  NOT NULL,
    [Codigo] int  NOT NULL
);
GO

-- Creating table 'Tareas_TareaOperaciones'
CREATE TABLE [ct].[Tareas_TareaOperaciones] (
    [DepartamentoCodigo] int  NOT NULL,
    [Codigo] int  NOT NULL
);
GO

-- Creating table 'Tareas_TareaSoporte'
CREATE TABLE [ct].[Tareas_TareaSoporte] (
    [DepartamentoCodigo] int  NOT NULL,
    [Codigo] int  NOT NULL
);
GO

-- Creating table 'FaseProyecto'
CREATE TABLE [ct].[FaseProyecto] (
    [Fases_Codigo] int  NOT NULL,
    [FaseProyecto_Fase_Codigo] int  NOT NULL
);
GO

-- Creating table 'UsuarioProyecto'
CREATE TABLE [ct].[UsuarioProyecto] (
    [Usuarios_Codigo] int  NOT NULL,
    [UsuarioProyecto_Usuario_Codigo] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Codigo] in table 'Proyectos'
ALTER TABLE [ct].[Proyectos]
ADD CONSTRAINT [PK_Proyectos]
    PRIMARY KEY CLUSTERED ([Codigo] ASC);
GO

-- Creating primary key on [Codigo] in table 'Prioridades'
ALTER TABLE [ct].[Prioridades]
ADD CONSTRAINT [PK_Prioridades]
    PRIMARY KEY CLUSTERED ([Codigo] ASC);
GO

-- Creating primary key on [Codigo] in table 'Departamentos'
ALTER TABLE [ct].[Departamentos]
ADD CONSTRAINT [PK_Departamentos]
    PRIMARY KEY CLUSTERED ([Codigo] ASC);
GO

-- Creating primary key on [Codigo] in table 'Tareas'
ALTER TABLE [ct].[Tareas]
ADD CONSTRAINT [PK_Tareas]
    PRIMARY KEY CLUSTERED ([Codigo] ASC);
GO

-- Creating primary key on [Codigo] in table 'Fases'
ALTER TABLE [ct].[Fases]
ADD CONSTRAINT [PK_Fases]
    PRIMARY KEY CLUSTERED ([Codigo] ASC);
GO

-- Creating primary key on [Codigo] in table 'Estados'
ALTER TABLE [ct].[Estados]
ADD CONSTRAINT [PK_Estados]
    PRIMARY KEY CLUSTERED ([Codigo] ASC);
GO

-- Creating primary key on [Codigo] in table 'Tipos'
ALTER TABLE [ct].[Tipos]
ADD CONSTRAINT [PK_Tipos]
    PRIMARY KEY CLUSTERED ([Codigo] ASC);
GO

-- Creating primary key on [Codigo] in table 'Usuarios'
ALTER TABLE [ct].[Usuarios]
ADD CONSTRAINT [PK_Usuarios]
    PRIMARY KEY CLUSTERED ([Codigo] ASC);
GO

-- Creating primary key on [Codigo] in table 'Registros'
ALTER TABLE [ct].[Registros]
ADD CONSTRAINT [PK_Registros]
    PRIMARY KEY CLUSTERED ([Codigo] ASC);
GO

-- Creating primary key on [Codigo] in table 'Tareas_TareaGestion'
ALTER TABLE [ct].[Tareas_TareaGestion]
ADD CONSTRAINT [PK_Tareas_TareaGestion]
    PRIMARY KEY CLUSTERED ([Codigo] ASC);
GO

-- Creating primary key on [Codigo] in table 'Tareas_TareaMantenimiento'
ALTER TABLE [ct].[Tareas_TareaMantenimiento]
ADD CONSTRAINT [PK_Tareas_TareaMantenimiento]
    PRIMARY KEY CLUSTERED ([Codigo] ASC);
GO

-- Creating primary key on [Codigo] in table 'Tareas_TareaOperaciones'
ALTER TABLE [ct].[Tareas_TareaOperaciones]
ADD CONSTRAINT [PK_Tareas_TareaOperaciones]
    PRIMARY KEY CLUSTERED ([Codigo] ASC);
GO

-- Creating primary key on [Codigo] in table 'Tareas_TareaSoporte'
ALTER TABLE [ct].[Tareas_TareaSoporte]
ADD CONSTRAINT [PK_Tareas_TareaSoporte]
    PRIMARY KEY CLUSTERED ([Codigo] ASC);
GO

-- Creating primary key on [Fases_Codigo], [FaseProyecto_Fase_Codigo] in table 'FaseProyecto'
ALTER TABLE [ct].[FaseProyecto]
ADD CONSTRAINT [PK_FaseProyecto]
    PRIMARY KEY NONCLUSTERED ([Fases_Codigo], [FaseProyecto_Fase_Codigo] ASC);
GO

-- Creating primary key on [Usuarios_Codigo], [UsuarioProyecto_Usuario_Codigo] in table 'UsuarioProyecto'
ALTER TABLE [ct].[UsuarioProyecto]
ADD CONSTRAINT [PK_UsuarioProyecto]
    PRIMARY KEY NONCLUSTERED ([Usuarios_Codigo], [UsuarioProyecto_Usuario_Codigo] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [PrioridadCodigo] in table 'Proyectos'
ALTER TABLE [ct].[Proyectos]
ADD CONSTRAINT [FK_ProyectoPrioridad]
    FOREIGN KEY ([PrioridadCodigo])
    REFERENCES [ct].[Prioridades]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProyectoPrioridad'
CREATE INDEX [IX_FK_ProyectoPrioridad]
ON [ct].[Proyectos]
    ([PrioridadCodigo]);
GO

-- Creating foreign key on [DepartamentoCodigo] in table 'Proyectos'
ALTER TABLE [ct].[Proyectos]
ADD CONSTRAINT [FK_ProyectoDepartamento]
    FOREIGN KEY ([DepartamentoCodigo])
    REFERENCES [ct].[Departamentos]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProyectoDepartamento'
CREATE INDEX [IX_FK_ProyectoDepartamento]
ON [ct].[Proyectos]
    ([DepartamentoCodigo]);
GO

-- Creating foreign key on [DepartamentoCodigo] in table 'Tareas_TareaGestion'
ALTER TABLE [ct].[Tareas_TareaGestion]
ADD CONSTRAINT [FK_DepartamentoTareaGestion]
    FOREIGN KEY ([DepartamentoCodigo])
    REFERENCES [ct].[Departamentos]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_DepartamentoTareaGestion'
CREATE INDEX [IX_FK_DepartamentoTareaGestion]
ON [ct].[Tareas_TareaGestion]
    ([DepartamentoCodigo]);
GO

-- Creating foreign key on [DepartamentoCodigo] in table 'Tareas_TareaMantenimiento'
ALTER TABLE [ct].[Tareas_TareaMantenimiento]
ADD CONSTRAINT [FK_DepartamentoTareaMantenimiento]
    FOREIGN KEY ([DepartamentoCodigo])
    REFERENCES [ct].[Departamentos]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_DepartamentoTareaMantenimiento'
CREATE INDEX [IX_FK_DepartamentoTareaMantenimiento]
ON [ct].[Tareas_TareaMantenimiento]
    ([DepartamentoCodigo]);
GO

-- Creating foreign key on [DepartamentoCodigo] in table 'Tareas_TareaOperaciones'
ALTER TABLE [ct].[Tareas_TareaOperaciones]
ADD CONSTRAINT [FK_DepartamentoTareaOperaciones]
    FOREIGN KEY ([DepartamentoCodigo])
    REFERENCES [ct].[Departamentos]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_DepartamentoTareaOperaciones'
CREATE INDEX [IX_FK_DepartamentoTareaOperaciones]
ON [ct].[Tareas_TareaOperaciones]
    ([DepartamentoCodigo]);
GO

-- Creating foreign key on [DepartamentoCodigo] in table 'Tareas_TareaSoporte'
ALTER TABLE [ct].[Tareas_TareaSoporte]
ADD CONSTRAINT [FK_DepartamentoTareaSoporte]
    FOREIGN KEY ([DepartamentoCodigo])
    REFERENCES [ct].[Departamentos]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_DepartamentoTareaSoporte'
CREATE INDEX [IX_FK_DepartamentoTareaSoporte]
ON [ct].[Tareas_TareaSoporte]
    ([DepartamentoCodigo]);
GO

-- Creating foreign key on [DepartamentoPeticionario_Codigo] in table 'Tareas'
ALTER TABLE [ct].[Tareas]
ADD CONSTRAINT [FK_TareaDepartamentoPeticionario]
    FOREIGN KEY ([DepartamentoPeticionario_Codigo])
    REFERENCES [ct].[Departamentos]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TareaDepartamentoPeticionario'
CREATE INDEX [IX_FK_TareaDepartamentoPeticionario]
ON [ct].[Tareas]
    ([DepartamentoPeticionario_Codigo]);
GO

-- Creating foreign key on [ProyectoAsociado_Codigo] in table 'Tareas'
ALTER TABLE [ct].[Tareas]
ADD CONSTRAINT [FK_TareaProyectoAsociado]
    FOREIGN KEY ([ProyectoAsociado_Codigo])
    REFERENCES [ct].[Proyectos]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TareaProyectoAsociado'
CREATE INDEX [IX_FK_TareaProyectoAsociado]
ON [ct].[Tareas]
    ([ProyectoAsociado_Codigo]);
GO

-- Creating foreign key on [Fases_Codigo] in table 'FaseProyecto'
ALTER TABLE [ct].[FaseProyecto]
ADD CONSTRAINT [FK_FaseProyecto_Fase]
    FOREIGN KEY ([Fases_Codigo])
    REFERENCES [ct].[Fases]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [FaseProyecto_Fase_Codigo] in table 'FaseProyecto'
ALTER TABLE [ct].[FaseProyecto]
ADD CONSTRAINT [FK_FaseProyecto_Proyecto]
    FOREIGN KEY ([FaseProyecto_Fase_Codigo])
    REFERENCES [ct].[Proyectos]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_FaseProyecto_Proyecto'
CREATE INDEX [IX_FK_FaseProyecto_Proyecto]
ON [ct].[FaseProyecto]
    ([FaseProyecto_Fase_Codigo]);
GO

-- Creating foreign key on [Tareas_Codigo] in table 'Fases'
ALTER TABLE [ct].[Fases]
ADD CONSTRAINT [FK_FaseTarea]
    FOREIGN KEY ([Tareas_Codigo])
    REFERENCES [ct].[Tareas]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_FaseTarea'
CREATE INDEX [IX_FK_FaseTarea]
ON [ct].[Fases]
    ([Tareas_Codigo]);
GO

-- Creating foreign key on [EstadoCodigo] in table 'Proyectos'
ALTER TABLE [ct].[Proyectos]
ADD CONSTRAINT [FK_EstadoProyecto]
    FOREIGN KEY ([EstadoCodigo])
    REFERENCES [ct].[Estados]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EstadoProyecto'
CREATE INDEX [IX_FK_EstadoProyecto]
ON [ct].[Proyectos]
    ([EstadoCodigo]);
GO

-- Creating foreign key on [UsuarioCodigo] in table 'Tareas'
ALTER TABLE [ct].[Tareas]
ADD CONSTRAINT [FK_UsuarioTarea]
    FOREIGN KEY ([UsuarioCodigo])
    REFERENCES [ct].[Usuarios]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UsuarioTarea'
CREATE INDEX [IX_FK_UsuarioTarea]
ON [ct].[Tareas]
    ([UsuarioCodigo]);
GO

-- Creating foreign key on [Usuarios_Codigo] in table 'UsuarioProyecto'
ALTER TABLE [ct].[UsuarioProyecto]
ADD CONSTRAINT [FK_UsuarioProyecto_Usuario]
    FOREIGN KEY ([Usuarios_Codigo])
    REFERENCES [ct].[Usuarios]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [UsuarioProyecto_Usuario_Codigo] in table 'UsuarioProyecto'
ALTER TABLE [ct].[UsuarioProyecto]
ADD CONSTRAINT [FK_UsuarioProyecto_Proyecto]
    FOREIGN KEY ([UsuarioProyecto_Usuario_Codigo])
    REFERENCES [ct].[Proyectos]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UsuarioProyecto_Proyecto'
CREATE INDEX [IX_FK_UsuarioProyecto_Proyecto]
ON [ct].[UsuarioProyecto]
    ([UsuarioProyecto_Usuario_Codigo]);
GO

-- Creating foreign key on [TareaCodigo] in table 'Registros'
ALTER TABLE [ct].[Registros]
ADD CONSTRAINT [FK_TareaRegistro]
    FOREIGN KEY ([TareaCodigo])
    REFERENCES [ct].[Tareas]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TareaRegistro'
CREATE INDEX [IX_FK_TareaRegistro]
ON [ct].[Registros]
    ([TareaCodigo]);
GO

-- Creating foreign key on [UsuarioResponsable_Usuario_Codigo] in table 'Usuarios'
ALTER TABLE [ct].[Usuarios]
ADD CONSTRAINT [FK_UsuarioResponsable]
    FOREIGN KEY ([UsuarioResponsable_Usuario_Codigo])
    REFERENCES [ct].[Departamentos]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UsuarioResponsable'
CREATE INDEX [IX_FK_UsuarioResponsable]
ON [ct].[Usuarios]
    ([UsuarioResponsable_Usuario_Codigo]);
GO

-- Creating foreign key on [TipoCodigo] in table 'Registros'
ALTER TABLE [ct].[Registros]
ADD CONSTRAINT [FK_TipoRegistro]
    FOREIGN KEY ([TipoCodigo])
    REFERENCES [ct].[Tipos]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TipoRegistro'
CREATE INDEX [IX_FK_TipoRegistro]
ON [ct].[Registros]
    ([TipoCodigo]);
GO

-- Creating foreign key on [DepartamentoCodigo] in table 'Usuarios'
ALTER TABLE [ct].[Usuarios]
ADD CONSTRAINT [FK_UsuarioDepartamento]
    FOREIGN KEY ([DepartamentoCodigo])
    REFERENCES [ct].[Departamentos]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UsuarioDepartamento'
CREATE INDEX [IX_FK_UsuarioDepartamento]
ON [ct].[Usuarios]
    ([DepartamentoCodigo]);
GO

-- Creating foreign key on [Codigo] in table 'Tareas_TareaGestion'
ALTER TABLE [ct].[Tareas_TareaGestion]
ADD CONSTRAINT [FK_TareaGestion_inherits_Tarea]
    FOREIGN KEY ([Codigo])
    REFERENCES [ct].[Tareas]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Codigo] in table 'Tareas_TareaMantenimiento'
ALTER TABLE [ct].[Tareas_TareaMantenimiento]
ADD CONSTRAINT [FK_TareaMantenimiento_inherits_Tarea]
    FOREIGN KEY ([Codigo])
    REFERENCES [ct].[Tareas]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Codigo] in table 'Tareas_TareaOperaciones'
ALTER TABLE [ct].[Tareas_TareaOperaciones]
ADD CONSTRAINT [FK_TareaOperaciones_inherits_Tarea]
    FOREIGN KEY ([Codigo])
    REFERENCES [ct].[Tareas]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Codigo] in table 'Tareas_TareaSoporte'
ALTER TABLE [ct].[Tareas_TareaSoporte]
ADD CONSTRAINT [FK_TareaSoporte_inherits_Tarea]
    FOREIGN KEY ([Codigo])
    REFERENCES [ct].[Tareas]
        ([Codigo])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------